package dao;

import entity.Music;
import util.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class LoveMusicDao {
    public boolean insertLoveMusic(int userId, int musicId) {
        Connection con = null;
        PreparedStatement prs = null;
        try {
            con = DBUtils.getConn();
            String sql = "insert into lovemusic(user_id,music_id) values (?,?)";
            prs = con.prepareStatement(sql);
            prs.setInt(1, userId);
            prs.setInt(2, musicId);
            int ret = prs.executeUpdate();
            if (ret == 1) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.getClose(con, prs, null);
        }
        return false;
    }

    /**
     * @param user_id
     * 查询当前用户 喜欢的音乐列表
     * @return
     */
    public List<Music> findLoveMusic(int user_id) {
        List<Music> musicList = new ArrayList<>();
        Connection con = null;
        PreparedStatement prs = null;
        ResultSet resultSet = null;

        try {
            con = DBUtils.getConn();
            //String sql = "select * from lovemusic where user_id=?";
            String sql = "select m.id as m_id,title,singer,time,url,userid from " +
                    "lovemusic lm,music m where m.id = lm.music_id and lm.user_id=?";
            //music : id title singer time url userid
            //lovemusic : id user_id music_id

            prs = con.prepareStatement(sql);
            prs.setInt(1,user_id);
            resultSet = prs.executeQuery();

            while (resultSet.next()) {
                Music music = new Music();
                music.setId(resultSet.getInt("m_id"));
                music.setTitle(resultSet.getString("title"));
                music.setSinger(resultSet.getString("singer"));
                music.setTime(resultSet.getString("time"));
                music.setUrl(resultSet.getString("url"));
                music.setUserId(resultSet.getInt("userid"));
                musicList.add(music);
            }

        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.getClose(con, prs, null);
        }
        return musicList;
    }

    /**
     * 预防重复喜欢：
     * @param user_id
     * @param musicID
     * @return
     */
    public boolean findLoveMusicByMusicIdAndUserId(int user_id,int musicID) {
        Connection con = null;
        PreparedStatement prs = null;
        ResultSet resultSet = null;
        try {
            con = DBUtils.getConn();
            String sql = "select * from lovemusic where user_id=? and music_id=?";
            prs = con.prepareStatement(sql);
            prs.setInt(1,user_id);
            prs.setInt(2,musicID);

            resultSet = prs.executeQuery();
            if(resultSet.next()) {
                return true;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.getClose(con, prs, null);
        }
        return false;
    }

    /**
     * 根据关键字查询喜欢的歌单
     * @param str
     * @return
     */
    public List<Music> findLoveMusicBykeyAndUID(String str,int user_id){
        Connection con = null;
        PreparedStatement prs = null;
        ResultSet resultSet = null;
        List<Music> musicList = new ArrayList<>();
        try {
            con = DBUtils.getConn();
            String sql = "select m.id as m_id,title,singer,time,url,userid from " +
                    "lovemusic lm,music m where m.id = lm.music_id and lm.user_id=? " +
                    "and title like ?";//'%str%'
            prs = con.prepareStatement(sql);
            prs.setInt(1,user_id);
            str = "%"+str+"%";
            prs.setString(2,str);

            System.out.println("sql:::" + prs.toString());

            resultSet = prs.executeQuery();
            while(resultSet.next()) {
                Music music = new Music();
                music.setId(resultSet.getInt("m_id"));
                music.setTitle(resultSet.getString("title"));
                music.setSinger(resultSet.getString("singer"));
                music.setTime(resultSet.getString("time"));
                music.setUrl(resultSet.getString("url"));
                music.setUserId(resultSet.getInt("userid"));
                musicList.add(music);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.getClose(con, prs, null);
        }
        return musicList;
    }

    public int removeLoveMusic(int userId,int musicId) {
        Connection con = null;
        PreparedStatement prs = null;
        try {
            con = DBUtils.getConn();
            String sql = "delete from lovemusic where user_id=? and music_id=?";
            prs = con.prepareStatement(sql);
            prs.setInt(1,userId);
            prs.setInt(2,musicId);

            int ret = prs.executeUpdate();
            if(ret == 1) {
                return 1;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.getClose(con, prs, null);
        }
        return 0;
    }

}
